package com.ccjq.shosestore.product.dao.impl;

import com.ccjq.shosestore.core.dbmanager.DbConnection;
import com.ccjq.shosestore.product.dao.ProductDao;
import com.ccjq.shosestore.product.model.BrandModel;
import com.ccjq.shosestore.product.model.ProductModel;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @describe
 * @author Ding Houwen
 * @time 2018年12月9日上午1:26:49
 * @project shosestore-product
 */
public class ProductDaoImpl implements ProductDao {
	/**
	 * 分页查询所有品牌
	 *
	 * */
	public List<ProductModel> queryAll(int pageSize, int startRow) {
		String s = "SELECT * FROM (SELECT PRODUCT_ID, PRICE, DESCRIPTION, MODEL, FEATURE, IMGPATH, NOVALTY_STATUS, PUBLISH_DATE, CATEGORY_ID, ROWNUM as n  FROM (SELECT PRODUCT_ID, PRICE, DESCRIPTION, MODEL, FEATURE, IMGPATH, NOVALTY_STATUS, PUBLISH_DATE, CATEGORY_ID FROM T_PRODUCT ORDER BY PUBLISH_DATE DESC))  WHERE n BETWEEN ? and ? ";
		List<ProductModel> productlist = new ArrayList<ProductModel>();
		Connection conn = DbConnection.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(s);
			ps.setInt(1, startRow);
			ps.setInt(2, pageSize + startRow - 1);
			ResultSet re = ps.executeQuery();

			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setCategory_id(re.getString(9));
				po.setPrice(re.getString(2));
				po.setDescription(re.getString(3));
				po.setModel(re.getString(4));
				po.setFeature(re.getString(5));
				po.setImgpath(re.getString(6));
				po.setNovalty_date(re.getString(7));
				po.setPublish_date(re.getString(8));
				po.setProduct_id(re.getString(1));
				productlist.add(po);
			}
			DbConnection.close(re, ps, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return productlist;
	}
	/**
	 * 分页查询具体品牌
	 *
	 * */
	public List<ProductModel> queryAll(String categoryId, int pageSize, int startRow) {
		String s = "SELECT * FROM (SELECT PRODUCT_ID, PRICE, DESCRIPTION, MODEL, FEATURE, IMGPATH, NOVALTY_STATUS, PUBLISH_DATE, CATEGORY_ID, ROWNUM as n  FROM (SELECT PRODUCT_ID, PRICE, DESCRIPTION, MODEL, FEATURE, IMGPATH, NOVALTY_STATUS, PUBLISH_DATE, CATEGORY_ID FROM T_PRODUCT WHERE CATEGORY_ID=? ORDER BY PUBLISH_DATE DESC))  WHERE n BETWEEN ? and ? ";

		List<ProductModel> productlist = new ArrayList<ProductModel>();
		Connection conn = DbConnection.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(s);
			ps.setString(1, categoryId);
			ps.setInt(2, startRow);
			ps.setInt(3, pageSize + startRow - 1);

			ResultSet re = ps.executeQuery();

			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setCategory_id(re.getString(9));
				po.setPrice(re.getString(2));
				po.setDescription(re.getString(3));
				po.setModel(re.getString(4));
				po.setFeature(re.getString(5));
				po.setImgpath(re.getString(6));
				po.setNovalty_date(re.getString(7));
				po.setPublish_date(re.getString(8));
				po.setProduct_id(re.getString(1));
				productlist.add(po);
			}
			DbConnection.close(re, ps, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return productlist;
	}

	/**
	 * 获取滚动区List
	 * */
	public List<ProductModel> getScrollList() {
		String s = "SELECT SCOTT.\"T_SLIDER\".\"DESCRIPTION\", SCOTT.\"T_SLIDER\".\"PRODUCT_ID\", SCOTT.\"T_SLIDER\".\"IMGPATH\", SCOTT.\"T_SLIDER\".\"AVAILABILITY\" FROM SCOTT.\"T_SLIDER\" WHERE SCOTT.\"T_SLIDER\".\"AVAILABILITY\" > 0  ";
		System.out.println("getScrollList SQL: " + s);
		List<ProductModel> productlist = new ArrayList<ProductModel>();
		try {
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setDescription(re.getString(1));
				po.setProduct_id(re.getString(2));
				po.setImgpath(re.getString(3));
				System.out.println("///"+po.getImgpath());

				productlist.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return productlist;
	}

	// Category_id,currentPage,pageMethod, totalRows
	/**
	 * 获取某品牌List ok
	 * */
	public List<ProductModel> getBrand(String Category_id, int startRow, int endRow) {

		//String sql="select * from t_product where category_id ="+Category_id;
		String s="select * from( select * from ( select product_id ,price ,description ,model ,feature,imgpath,novalty_status,publish_date,category_id,rownum rn from t_product t where category_id ='"+Category_id+"')t1 where t1.rn>="+startRow+")t2 where t2.rn<="+endRow;
		System.out.println(s);
		// 拿到所有数据的list
		List<ProductModel> productlist = new ArrayList<ProductModel>();
		try {
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setProduct_id(re.getString(1));
				po.setDescription(re.getString(2));
				po.setPrice(re.getString(3));
				po.setModel(re.getString(4));
				po.setFeature(re.getString(5));
				po.setImgpath(re.getString(6));
				po.setNovalty_date(re.getString(7));
				po.setPublish_date(re.getString(8));
				po.setCategory_id(re.getString(9));
				productlist.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return productlist;

	}
	/**
	 * 获取所有List() ok
	 * */

	public List<ProductModel> getProductList(int startRow, int endRow) {
		String s="select * from(\n" +
				"select * from(\n" +
				"select product_id ,price ,description ,model ,feature,imgpath,novalty_status,publish_date,category_id,rownum rn  \n" +
				"from t_product t \n" +
				"where product_id in(select product_id from t_stock where quantity>0)\n" +
				")t1 where t1.rn>=" +startRow
				+
				")t2 where t2.rn<="+endRow;
		System.out.println(s);

		// 拿到所有数据的list
		List<ProductModel> productlist = new ArrayList<ProductModel>();
		try {
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setProduct_id(re.getString(1));
				po.setPrice(re.getString(2));
				po.setDescription(re.getString(3));
				po.setModel(re.getString(4));
				po.setFeature(re.getString(5));
				po.setImgpath(re.getString(6));
				po.setNovalty_date(re.getString(7));
				po.setPublish_date(re.getString(8));
				po.setCategory_id(re.getString(9));
				productlist.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		// 返回请求页数的list
		return productlist;
	}
	/**
	 * 获取所有List行数(某品牌)
	 * */
	public int getBrandRow(String Category_id) {
		int row = 0;
		try {
			String s = "SELECT COUNT(*) FROM T_PRODUCT WHERE \"CATEGORY_ID\"='" + Category_id + "'";
			System.out.println("getBrandRow SQL:" + s);
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				row = re.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;

	}

	/**
	 * 获取热卖List no
	 * */
	public List<ProductModel> getFashionList() {
		String s = "SELECT SCOTT.T_PRODUCT.\"PRODUCT_ID\", SCOTT.T_PRODUCT.\"PRICE\", SCOTT.T_PRODUCT.\"DESCRIPTION\", SCOTT.T_PRODUCT.\"MODEL\", SCOTT.T_PRODUCT.\"FEATURE\", SCOTT.T_PRODUCT.\"IMGPATH\", SCOTT.T_PRODUCT.\"NOVALTY_STATUS\", SCOTT.T_PRODUCT.\"PUBLISH_DATE\", SCOTT.T_PRODUCT.\"CATEGORY_ID\" FROM SCOTT.T_PRODUCT WHERE SCOTT.T_PRODUCT.\"PRODUCT_ID\" IN (SELECT \"PRODUCT_ID\" FROM (SELECT SUM(SCOTT.T_ORDER_PRODUCT.\"QUANTITY\"), SCOTT.T_ORDER_PRODUCT.\"PRODUCT_ID\" FROM SCOTT.T_ORDER_PRODUCT GROUP BY SCOTT.T_ORDER_PRODUCT.\"PRODUCT_ID\" ORDER BY SUM( SCOTT.T_ORDER_PRODUCT.\"QUANTITY\" ) DESC)  WHERE ROWNUM<5)  ";
		List<ProductModel> productlist = new ArrayList<ProductModel>();
		try {
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setCategory_id(re.getString(9));
				po.setPrice(re.getString(2));
				po.setDescription(re.getString(3));
				po.setModel(re.getString(4));
				po.setFeature(re.getString(5));
				po.setImgpath(re.getString(6));
				po.setNovalty_date(re.getString(7));
				po.setPublish_date(re.getString(8));
				po.setProduct_id(re.getString(1));
				productlist.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.print("getFashionList SQL：" + s);
		return productlist;
	}

	/**
	 * 获取新品List ok
	 * */
	public List<ProductModel> getNewproductList() {
		String s="select * from (select * from t_product order by publish_date desc) where rownum<=6";
		List<ProductModel> productlist = new ArrayList<ProductModel>();
		try {
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setCategory_id(re.getString(9));
				po.setPrice(re.getString(3));
				po.setDescription(re.getString(2));
				po.setModel(re.getString(4));
				po.setFeature(re.getString(5));
				po.setImgpath(re.getString(6));
				po.setNovalty_date(re.getString(7));
				po.setPublish_date(re.getString(8));
				po.setProduct_id(re.getString(1));
				productlist.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("getNewproductList SQL：" + s);
		return productlist;
	}

	/**
	 * 获取单品ProductModel
	 * */
	public ProductModel getProductText(String product_id) {
		String s = "SELECT SCOTT.T_PRODUCT.\"PRODUCT_ID\", SCOTT.T_PRODUCT.\"PRICE\", SCOTT.T_PRODUCT.\"DESCRIPTION\", SCOTT.T_PRODUCT.\"MODEL\", SCOTT.T_PRODUCT.\"FEATURE\", SCOTT.T_PRODUCT.\"IMGPATH\", SCOTT.T_PRODUCT.\"NOVALTY_STATUS\", SCOTT.T_PRODUCT.\"PUBLISH_DATE\", SCOTT.T_PRODUCT.\"CATEGORY_ID\" FROM SCOTT.T_PRODUCT WHERE SCOTT.T_PRODUCT.\"PRODUCT_ID\" ='"

		+ product_id + "' ";

		List<ProductModel> productlist = new ArrayList<ProductModel>();
		try {
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setCategory_id(re.getString(9));
				po.setPrice(re.getString(2));
				po.setDescription(re.getString(3));
				po.setModel(re.getString(4));
				po.setFeature(re.getString(5));
				po.setImgpath(re.getString(6));
				po.setNovalty_date(re.getString(7));
				po.setPublish_date(re.getString(8));
				po.setProduct_id(re.getString(1));
				productlist.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		ProductModel productText = new ProductModel();
		for (ProductModel p : productlist) {
			productText.setCategory_id(p.getCategory_id());
			productText.setPrice(p.getPrice());
			productText.setDescription(p.getDescription());
			productText.setModel(p.getModel());
			productText.setFeature(p.getFeature());
			productText.setImgpath(p.getImgpath());
			productText.setNovalty_date(p.getNovalty_date());
			productText.setPublish_date(p.getPublish_date());
			productText.setProduct_id(p.getProduct_id());
		}
		return productText;
	}

	/**
	 * 根据商品ID查库存
	 * */
	public int getQuantity(String product_id) {

		int quantity = 0;
		try {

			String s = "SELECT SCOTT.T_STOCK.\"QUANTITY\" FROM SCOTT.T_STOCK WHERE SCOTT.T_STOCK.\"PRODUCT_ID\" = '"

			+ product_id + "'  ";

			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);

			while (re.next()) {
				quantity = re.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return quantity;
	}

	/**
	 * 根据商品的品牌ID查品牌名 ok
	 * */
	public String getName(String ategory_id) {

		String name = null;
		try {
			String s = "SELECT	SCOTT.T_CATEGORY.\"NAME\" FROM	SCOTT.T_CATEGORY WHERE	SCOTT.T_CATEGORY.\"CATEGORY_ID\" = '" + ategory_id + "'";
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);

			while (re.next()) {
				name = re.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return name;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ccjq.shosestore.product.dao.ProductDao#getBrowseList()
	 */
	/**
	 * 最近浏览Lis
	 * */
	public List<ProductModel> getBrowseList() {
		String s = "SELECT SCOTT.T_PRODUCT.\"PRODUCT_ID\", SCOTT.T_PRODUCT.\"PRICE\", SCOTT.T_PRODUCT.\"DESCRIPTION\", SCOTT.T_PRODUCT.\"MODEL\", SCOTT.T_PRODUCT.\"FEATURE\", SCOTT.T_PRODUCT.\"IMGPATH\", SCOTT.T_PRODUCT.\"NOVALTY_STATUS\", SCOTT.T_PRODUCT.\"PUBLISH_DATE\", SCOTT.T_PRODUCT.\"CATEGORY_ID\" FROM SCOTT.T_PRODUCT WHERE SCOTT.T_PRODUCT.\"PRODUCT_ID\" IN (SELECT \"PRODUCT_ID\" FROM (SELECT SUM(SCOTT.T_ORDER_PRODUCT.\"QUANTITY\"), SCOTT.T_ORDER_PRODUCT.\"PRODUCT_ID\" FROM SCOTT.T_ORDER_PRODUCT GROUP BY SCOTT.T_ORDER_PRODUCT.\"PRODUCT_ID\" ORDER BY SUM( SCOTT.T_ORDER_PRODUCT.\"QUANTITY\" ) DESC)  WHERE ROWNUM<4)  ";


		List<ProductModel> productlist = new ArrayList<ProductModel>();
		try {
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				ProductModel po = new ProductModel();
				po.setCategory_id(re.getString(9));
				po.setPrice(re.getString(2));
				po.setDescription(re.getString(3));
				po.setModel(re.getString(4));
				po.setFeature(re.getString(5));
				po.setImgpath(re.getString(6));
				po.setNovalty_date(re.getString(7));
				po.setPublish_date(re.getString(8));
				po.setProduct_id(re.getString(1));
				productlist.add(po);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.print("getBrowseList SQL： " + s);
		return productlist;
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ccjq.shosestore.product.dao.ProductDao#getProductListRow()
	 */
	/**
	 * 获取所有List行数
	 * */
	public int getProductListRow() {
		int row = 0;

		try {
			//String s = " SELECT COUNT(*)  FROM 	SCOTT.T_PRODUCT  WHERE 	PRODUCT_ID IN ( SELECT PRODUCT_ID FROM SCOTT.T_STOCK WHERE SCOTT.T_STOCK.QUANTITY > 0 )  ";
			String s="select count(*) from t_product where product_id in (select product_id from t_stock where quantity>0)";
			Connection conn = DbConnection.getConnection();
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			if (re.next()) {
				row = re.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return row;
	}
	/**
	 * 获取库存
	 * */
	public String getStock(String id) {
		String s = "SELECT  SUM(SCOTT.T_ORDER_PRODUCT.QUANTITY) FROM SCOTT.T_ORDER_PRODUCT WHERE SCOTT.T_ORDER_PRODUCT.PRODUCT_ID='" + id + "' ";
		Connection conn = DbConnection.getConnection();
		Statement stat;
		String stock = "";
		try {
			stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);
			while (re.next()) {
				stock = re.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return stock;

	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see com.ccjq.shosestore.product.dao.ProductDao#br()
	 */
	/**
	 * 获取所有品牌信息
	 * @throws SQLException
	 * */
	public List<BrandModel> getBrandModel() {
		String s = "SELECT SCOTT.T_CATEGORY.CATEGORY_ID, SCOTT.T_CATEGORY.NAME FROM SCOTT.T_CATEGORY ";
		Connection conn = DbConnection.getConnection();
		List<BrandModel> brandlist = new ArrayList<BrandModel>();

		try {
			Statement stat = conn.createStatement();
			ResultSet re = stat.executeQuery(s);

			while (re.next()) {
				BrandModel brand = new BrandModel();
				brand.setCategory_id(re.getString(1));
				brand.setName(re.getString(2));
				brandlist.add(brand);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return brandlist;
	}

}
